#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class NavigationMenuDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new NavigationMenu instance filled with the DataReader's current record data 
	     /// </summary>
	     private NavigationMenu GetNavigationMenuFromReader(IDataReader dr)
	     {
		     NavigationMenu obj = new NavigationMenu();
		     obj.MenuID = (dr["MenuID"] is DBNull) ? Int32.MinValue :  (Int32)dr["MenuID"];
		     obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue :  (Int32)dr["CateID"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.Title_en = (dr["Title_en"] is DBNull) ? String.Empty :  (String)dr["Title_en"];
		     obj.Content = (dr["Content"] is DBNull) ? String.Empty :  (String)dr["Content"];
		     obj.Content_en = (dr["Content_en"] is DBNull) ? String.Empty :  (String)dr["Content_en"];
		     obj.LinkText = (dr["LinkText"] is DBNull) ? String.Empty :  (String)dr["LinkText"];
		     obj.Target = (dr["Target"] is DBNull) ? String.Empty :  (String)dr["Target"];
		     obj.IsActive = (dr["IsActive"] is DBNull) ? false :  (Boolean)dr["IsActive"];
		     obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue :  (Int32)dr["DisplayOrder"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of NavigationMenu with the data read from the input DataReader
	     /// </summary>
	     private List<NavigationMenu> GetNavigationMenuCollectionFromReader(IDataReader dr)
	     {
		     List<NavigationMenu> obj = new List<NavigationMenu>();
		     while(dr.Read())
			     obj.Add(GetNavigationMenuFromReader(dr));
		     return obj;
	     }

	     public int Insert(NavigationMenu obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@MenuID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
		     myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
		     myCommand.Parameters.Add("@Content_en", SqlDbType.NText).Value = obj.Content_en;
		     myCommand.Parameters.Add("@LinkText", SqlDbType.VarChar).Value = obj.LinkText;
		     myCommand.Parameters.Add("@Target", SqlDbType.VarChar).Value = obj.Target;
		     myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
		     myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@MenuID"].Value; 
		     }
	     }

	     public bool Update(NavigationMenu obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@MenuID", SqlDbType.Int).Value = obj.MenuID;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
		     myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
		     myCommand.Parameters.Add("@Content_en", SqlDbType.NText).Value = obj.Content_en;
		     myCommand.Parameters.Add("@LinkText", SqlDbType.VarChar).Value = obj.LinkText;
		     myCommand.Parameters.Add("@Target", SqlDbType.VarChar).Value = obj.Target;
		     myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;
		     myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 MenuID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@MenuID", SqlDbType.Int).Value = MenuID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the Job_NavigationMenu with the specified ID
	     /// </summary>
	     public NavigationMenu GetNavigationMenuByID(Int32 MenuID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_GetNavigationMenuByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@MenuID", SqlDbType.Int).Value = MenuID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetNavigationMenuFromReader(dr) : null);
		     }
	     }

	     //Get All NavigationMenu
	     public List<NavigationMenu> GetAllNavigationMenu(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_GetAllNavigationMenu", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetNavigationMenuCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  NavigationMenu
	     public int GetCountNavigationMenu()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_GetCountNavigationMenu", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	     // Get NavigationMenuByCateID
	     public List<NavigationMenu> GetNavigationMenuByCateID(Int32 CateID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_GetNavigationMenuByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetNavigationMenuCollectionFromReader(dr);
		     }
	     }

	     // Get NavigationMenuCountByCateID
	     public int GetNavigationMenuCountByCateID(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NavigationMenu_GetNavigationMenuCountByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
